03. UDF(User Defince Function)

BigQuery의 UDF 함수는 사용자가 정의한 기능을 구현할 수 있는 함수로, 자바스크립트를 기반으로 만들어집니다.
복잡한 조건식 혹은 문자열 파싱 등과 같은 기능은 순수 SQL로만 구현하기 어렵기 때문에 UDF로 구현하곤 합니다
Map 함수를 구현할 떄 사용합니다
input 스키마를 기반으로 다른 output 스키마를 return할 수 있습니다

UDF Editor를 누르면 UDF를 정의할 수 있습니다 ( 참고로 legacySQL일 경우 UDF Editor 버튼이 활성화 됩니다 )
StandardSQL UDF는 해당 문서에서 확인할 수 있습니다
때마침 BigQuery에서 제공하는 UDF의 구조입니다

// Example user-defined function, documentation: https://goo.gl/6KR8O0
// Sample SQL: SELECT outputA, outputB FROM (passthrough(SELECT "abc" AS inputA, "def" AS inputB))

/*
function passthroughExample(row, emit) {
  emit({outputA: row.inputA, outputB: row.inputB});
}

bigquery.defineFunction(
  'passthrough',                           // UDF의 이름
  ['inputA', 'inputB'],                    // input columns의 이름입니다
  [{'name': 'outputA', 'type': 'string'},  // Output schema를 설정해주세요
   {'name': 'outputB', 'type': 'string'}],
  passthroughExample                       // Reference to JavaScript UDF
);
*/

UDF 설명

UDF는 총 3 part로 나뉩니다
UDF definition : row와 emit 객체를 받아 행의 일부에서 함수를 실행하는 부분입니다
Helper Function : emitter function에서 호출할 수 있는 작업 함수를 정의합니다. Error handling도 함께 해줍니다
Registrtion Function : emmiter function과 input/output 스키마를 연결합니다

UDF Structure

// UDF definition
function function_name(row, emit) {
  emit(output data);
}

// Helper function with error handling
function helpter_function_name(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'function_name',  
  ['input column'], 

  // JSON representation of the output schema
  [{output_key: 'output_value'}],

  function_name  
);

urlDecode UDF function

urlDecode하는 UDF 함수는 아래와 같습니다

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

UDF Editor에 위 코드를 넣고 Query Editor로 넘어오고 아래와 같은 쿼리를 작성했습니다

SELECT requests, title
FROM
urlDecode(
  SELECT
    title, sum(requests) AS num_requests
  FROM
    [fh-bigquery:wikipedia.pagecounts_201504]
  WHERE language = 'fr'
  GROUP EACH BY title
)
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

Inline UDF라고 표시되며, 데이터의 양은 300GB인데 쿼리 비용은 약 1.5달러입니다..! (BQ Mate라는 크롬 확장 프로그램을 설치하면 예상 가격이 나옵니다)
UDF 함수는 비용이 좀 나가는 편입니다
따라서 충분한 테스트 후에 실제 데이터에 돌리는 것을 추천드리고 싶습니다. UDF 함수를 테스트할 수 있는 곳을 통해 실험해본 후, 적용하시는 것을 추천드립니다
저는 그냥 data를 python으로 가지고 와서 전처리하는 편입니다

UDF Editor에 정의하지 않고 사용하는 방법

SELECT outputA
FROM JS(
// input table
(
  SELECT text2 as inputA 
  FROM 
    (SELECT 'mikhail' AS text2),
    (SELECT 'mike' AS text2),
    (SELECT 'michael' AS text2),
    (SELECT 'javier' AS text2),
    (SELECT 'thomas' AS text2)
)

// input columns
, inputA

// output schema
, "[{name: 'outputA', type:'string'}]"

// function
, "function(r, emit) {

    emit({
      outputA: 'XX ' + r.inputA + ' XX'
    });

  }"
)

output

outputA
XX mikhail XX
XX mike XX
XX michael XX
XX javier XX
XX thomas XX